﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using NewDDSys.IDAL;
using NewDDSys.Entity;
using NewDDSys.Utility;
using System.Text;

namespace NewDDSys.ORADAL
{
    public class XkcdtDAL : IXkcdtDAL
    {
        public int Add(XkcdtInfo dt)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":dtnum", OracleType.VarChar, dt.Dtnum),
                DALHelper.MakeInParam(":num", OracleType.VarChar, dt.Num),
                DALHelper.MakeInParam(":banzu", OracleType.VarChar, dt.Banzu),
                DALHelper.MakeInParam(":tianbao", OracleType.VarChar, dt.Tianbao),
                DALHelper.MakeInParam(":kday", OracleType.VarChar, dt.Kday)
            };
            return (DALHelper.RunCommand(@"insert into dd.XKCDT (dtnum, num, banzu, tianbao, kday) values (:dtnum, :num, :banzu, :tianbao, to_date(:kday,'yyyy-mm-dd hh24:mi:ss'))", prams));

        }
        public int Update(string dtnum, decimal kn, decimal dk)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":dtnum", OracleType.VarChar, dtnum),
                DALHelper.MakeInParam(":kn", OracleType.Number, kn),
                DALHelper.MakeInParam(":dk", OracleType.Number, dk)
            };
            return (DALHelper.RunCommand(@"update dd.XKCDT set kn = :kn, dk = :dk where dtnum = :dtnum", prams));

        }
        public int UpdateKN(string dtnum, decimal kn)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":dtnum", OracleType.VarChar, dtnum),
                DALHelper.MakeInParam(":kn", OracleType.Number, kn)
            };
            return (DALHelper.RunCommand(@"update dd.XKCDT set kn = :kn where dtnum = :dtnum", prams));
        }
        public int UpdateDK(string dtnum, decimal dk)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":dtnum", OracleType.VarChar, dtnum),
                DALHelper.MakeInParam(":dk", OracleType.Number, dk)
            };
            return (DALHelper.RunCommand(@"update dd.XKCDT set dk = :dk where dtnum = :dtnum", prams));
        }
        public int Del(string dtnum)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":dtnum", OracleType.VarChar, dtnum)
            };
            return (DALHelper.RunCommand(@"delete from dd.XKCDT where DTNUM = :dtnum", prams));

        }
        public DataSet Select(string dtnum)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":dtnum", OracleType.VarChar, dtnum)
            };
            return (DALHelper.RunProcReturn(@"select * from dd.XKCDT where dtnum = :dtnum", prams, "jl"));
        }

        public DataSet SelectAll(string ku)
        {
            string nowtime = DateTime.Now.ToShortTimeString().ToString();
            string time1 = "08:00:00";
            string time2 = "20:00:00";
            DateTime dt1 = Convert.ToDateTime(nowtime);
            DateTime dt2 = Convert.ToDateTime(time1);
            DateTime dt3 = Convert.ToDateTime(time2);
            string bt = "";
            string et = "";
            if (DateTime.Compare(dt1, dt2) >= 0 && DateTime.Compare(dt1, dt3) <= 0)
            {
                bt = dt2.ToString();
                et = dt3.ToString();
            }
            else if (DateTime.Compare(dt1, dt2) < 0)
            {
                bt = dt3.AddDays(-1).ToString();
                et = dt1.ToString();
            }
            else
            {
                bt = dt3.ToString();
                et = dt1.ToString();
            }
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":ku", OracleType.VarChar, ku),
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            return (DALHelper.RunProcReturn(@"select dt.dtnum as dtnum, jg.ku as ku, jg.pinzhong as pinzhong, jg.guige as guige, dt.kday as kday, dt.banzu as banzu, dt.tianbao as tianbao, dt.kn as kn, dt.dk as dk from dd.XKCDT dt, dd.KC jg where dt.num = jg.num and jg.ku = :ku and dt.kday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and dt.kday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss')", prams, "jlall"));

        }
        public DataSet SelectPG(string ku)
        {
            string nowtime = DateTime.Now.ToShortTimeString().ToString();
            string time1 = "08:00:00";
            string time2 = "20:00:00";
            DateTime dt1 = Convert.ToDateTime(nowtime);
            DateTime dt2 = Convert.ToDateTime(time1);
            DateTime dt3 = Convert.ToDateTime(time2);
            string bt = "";
            string et = "";
            if (DateTime.Compare(dt1, dt2) >= 0 && DateTime.Compare(dt1, dt3) <= 0)
            {
                bt = dt2.ToString();
                et = dt3.ToString();
            }
            else if (DateTime.Compare(dt1, dt2) < 0)
            {
                bt = dt3.AddDays(-1).ToString();
                et = dt1.ToString();
            }
            else
            {
                bt = dt3.ToString();
                et = dt1.ToString();
            }
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":ku", OracleType.VarChar, ku),
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            return (DALHelper.RunProcReturn(@"select num, pinzhong, guige from dd.KC where ku = :ku and num not in (select num from dd.XKCDT where kday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and kday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss'))", prams, "pg"));

        }
        public string KuKC(string cday, string ku)
        {
            
            string et = Convert.ToDateTime(cday).AddHours(8).ToString();
            string bt = Convert.ToDateTime(et).AddHours(-12).ToString();
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":ku", OracleType.VarChar, ku),
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(dt.kn) from dd.XKCDT dt, dd.KC jg where dt.num = jg.num and jg.ku = :ku and dt.kday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and dt.kday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss')", prams);
            return obj.ToString();

        }
        public string JDXC(string cday)
        {
            
            string et = Convert.ToDateTime(cday).AddHours(8).ToString();
            string bt = Convert.ToDateTime(et).AddHours(-12).ToString();
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(dt.dk) from dd.XKCDT dt, dd.KC jg where dt.num = jg.num and jg.pinzhong in ('普碳线材', '品种线材') and dt.kday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and dt.kday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss')", prams);
            return obj.ToString();

        }
        public string PTkc(string cday)
        {
            string et = Convert.ToDateTime(cday).AddHours(8).ToString();
            string bt = Convert.ToDateTime(et).AddHours(-12).ToString();
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(dt.dk + dt.kn) from dd.XKCDT dt, dd.KC jg where dt.num = jg.num and jg.pinzhong in ('普碳线材') and dt.kday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and dt.kday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss')", prams);
            return obj.ToString();
        }
        public string PZkc(string cday)
        {
            string et = Convert.ToDateTime(cday).AddHours(8).ToString();
            string bt = Convert.ToDateTime(et).AddHours(-12).ToString();
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(dt.dk + dt.kn) from dd.XKCDT dt, dd.KC jg where dt.num = jg.num and jg.pinzhong in ('品种线材') and dt.kday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and dt.kday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss')", prams);
            return obj.ToString();
        }
        public string ZKC(string cday)
        {
            string et = Convert.ToDateTime(cday).AddHours(8).ToString();
            string bt = Convert.ToDateTime(et).AddHours(-12).ToString();
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(dk + kn) from dd.XKCDT where kday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and kday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss')", prams);
            if (obj == null)
                return "";
            else
                return obj.ToString();
        }
        
    }
}
